﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace AutoCode.Win
{
    /// <summary>
    /// 模块编号：ND.Tool.DBSHelper
    /// 作用：数据库结构操作使用类
    /// 作者：牛达
    /// 编写日期：2009-09-23
    /// </summary>
    public class DBSHelper
    {
        #region Sql数据库操作通用方法

        /// <summary>
        /// 创建数据库（Sql）
        /// </summary>
        /// <param name="p_filePath">文件路径</param>
        /// <param name="p_fileName">文件名</param>
        public void CreateSqlDB(string p_filePath, string p_fileName)
        {
            SqlConnection cn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=;Data Source=localhost;");
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            string sql = "CREATE DATABASE " + p_fileName + " ON PRIMARY"
                + "(name=" + p_fileName + "_data, filename = '" + HttpContext.Current.Server.MapPath(p_filePath) + p_fileName + "_data.mdf', size=3,"
                + "maxsize=5, filegrowth=10%)log on"
                + "(name=" + p_fileName + "_log, filename='" + HttpContext.Current.Server.MapPath(p_filePath) + p_fileName + "_log.ldf',size=3,"
                + "maxsize=20,filegrowth=1)";
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }

        /// <summary>
        /// 删除数据库（Sql）
        /// </summary>
        /// <param name="p_sqlDBName">数据库名称</param>
        /// <param name="p_cn">连接数据库</param>
        public void DropSqlDB(string p_sqlDBName, SqlConnection p_cn)
        {
            string sql = "DROP DATABASE " + p_sqlDBName;
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 连接数据库（Sql）
        /// </summary>
        /// <param name="p_sqlDBName">数据库名称</param>
        /// <param name="p_sqlPassword">登入密码</param>
        /// <param name="p_sqlUsername">登入用户名</param>
        /// <returns></returns>
        public SqlConnection LinkSqlDB(string p_sqlDBName, string p_sqlPassword, string p_sqlUsername)
        {
            SqlConnection cn = new SqlConnection("server=60.190.216.49;database=" + p_sqlDBName + ";user id=" + p_sqlUsername + ";password=" + p_sqlPassword + ";min pool size=4;max pool size=4;");
            try
            {
                cn.Open();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return cn;
        }

        /// <summary>
        /// 创建数据表（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_cn">连接数据库</param>
        public void CreateSqlTable(string p_tableName, SqlConnection p_cn)
        {
            string sql = "CREATE TABLE " + p_tableName + "(ID bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED)";
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 修改数据表名称（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_newTableName">新数据表名</param>
        /// <param name="p_cn">连接数据库</param>
        public void AlterSqlTableName(string p_tableName, string p_newTableName, SqlConnection p_cn)
        {
            string sql = "EXEC sp_rename '" + p_tableName + "', '" + p_newTableName + "'";
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 删除数据表（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_cn">连接数据库</param>
        public void DropSqlTable(string p_tableName, SqlConnection p_cn)
        {
            string sql = "DROP TABLE " + p_tableName;
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 读数据库的所有表名（Sql）
        /// </summary>
        /// <param name="p_cn">连接数据库</param>
        /// <returns></returns>
        public string ReadSqlTableName(SqlConnection p_cn)
        {
            string strTables = "";
            try
            {
                DataTable dt = p_cn.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if (row[3].ToString() == "BASE TABLE" && row[2].ToString() != "dtproperties")
                        strTables += row[2].ToString() + "|";
                }
                if (strTables != "")
                {
                    strTables = Common.CutEndStr(strTables, "|");
                }
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }
            return strTables;
        }

        /// <summary>
        /// 创建字段（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_fieldName">字段名</param>
        /// <param name="p_fieldType">字段类型</param>
        /// <param name="p_fieldSize">字段大小</param>
        /// <param name="p_cn">连接数据库</param>
        public void CreateSqlField(string p_tableName, string p_fieldName, string p_fieldDescription, int p_fieldType, int p_fieldSize, SqlConnection p_cn)
        {
            string defaultValue = "Null";
            string fieldType = "";
            switch (p_fieldType)
            {
                case 0: //bigint
                    fieldType = "bigint";
                    break;
                case 1: //binary
                    fieldType = "binary";
                    break;
                case 2: //bit
                    fieldType = "bit";
                    break;
                case 3: //char
                    fieldType = "char(" + p_fieldSize + ")";
                    break;
                case 4: //datetime
                    fieldType = "datetime";
                    defaultValue = "NOT NULL DEFAULT (getdate())";
                    break;
                case 5: //decimal
                    fieldType = "decimal";
                    break;
                case 6: //float
                    fieldType = "float";
                    break;
                case 7: //image
                    fieldType = "image";
                    break;
                case 8: //int
                    fieldType = "int";
                    break;
                case 9: //money
                    fieldType = "money";
                    break;
                case 10: //nchar
                    fieldType = "nchar(" + p_fieldSize + ")";
                    break;
                case 11: //ntext
                    fieldType = "ntext";
                    break;
                case 12: //numeric
                    fieldType = "numeric";
                    break;
                case 13: //nvarchar
                    fieldType = "nvarchar(" + p_fieldSize + ")";
                    break;
                case 14: //real
                    fieldType = "real";
                    break;
                case 15: //smalldatetime
                    fieldType = "smalldatetime";
                    defaultValue = "NOT NULL DEFAULT (getdate())";
                    break;
                case 16: //smallint
                    fieldType = "smallint";
                    break;
                case 17: //smallmoney
                    fieldType = "smallmoney";
                    break;
                case 18: //sql_variant
                    fieldType = "sql_variant";
                    break;
                case 19: //text
                    fieldType = "text";
                    break;
                case 20: //timestamp
                    fieldType = "timestamp";
                    break;
                case 21: //tinyint
                    fieldType = "tinyint";
                    break;
                case 22: //uniqueidentifier
                    fieldType = "uniqueidentifier";
                    break;
                case 23: //varbinary
                    fieldType = "varbinary";
                    break;
                case 24: //varchar
                    fieldType = "varchar(" + p_fieldSize + ")";
                    break;
            }
            string sql1 = "ALTER TABLE " + p_tableName + " ADD " + p_fieldName + " " + fieldType + " " + defaultValue;
            ExecuteSQLStmt(sql1, p_cn);
            string sql2 = "sp_addextendedproperty N'MS_Description', N'" + p_fieldDescription + "', 'USER', N'dbo', 'TABLE', N'" + p_tableName + "', 'COLUMN', N'" + p_fieldName + "'";
            ExecuteSQLStmt(sql2, p_cn);
        }

        /// <summary>
        /// 删除字段（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_fieldName">字段名</param>
        /// <param name="p_cn">连接数据库</param>
        public void DropSqlField(string p_tableName, string p_fieldName, SqlConnection p_cn)
        {
            string sql = "ALTER TABLE " + p_tableName + " DROP COLUMN " + p_fieldName;
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 修改字段名称（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_fieldName">字段名</param>
        /// <param name="p_newFieldName">新字段名</param>
        /// <param name="p_cn">连接数据库</param>
        public void AlterSqlFieldName(string p_tableName, string p_fieldName, string p_newFieldName, SqlConnection p_cn)
        {
            string sql = "EXEC sp_rename '" + p_tableName + ".[" + p_fieldName + "]', '" + p_newFieldName + "'";
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 修改字段类型（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_fieldName">字段名</param>
        /// <param name="p_fieldType">字段类型</param>
        /// <param name="p_fieldSize">字段大小</param>
        /// <param name="p_cn">连接数据库</param>
        public void AlterSqlFieldType(string p_tableName, string p_fieldName, int p_fieldType, int p_fieldSize, SqlConnection p_cn)
        {
            string fieldType = "";
            switch (p_fieldType)
            {
                case 0: //bigint
                    fieldType = "bigint";
                    break;
                case 1: //binary
                    fieldType = "binary";
                    break;
                case 2: //bit
                    fieldType = "bit";
                    break;
                case 3: //char
                    fieldType = "char(" + p_fieldSize + ")";
                    break;
                case 4: //datetime
                    fieldType = "datetime";
                    break;
                case 5: //decimal
                    fieldType = "decimal";
                    break;
                case 6: //float
                    fieldType = "float";
                    break;
                case 7: //image
                    fieldType = "image";
                    break;
                case 8: //int
                    fieldType = "int";
                    break;
                case 9: //money
                    fieldType = "money";
                    break;
                case 10: //nchar
                    fieldType = "nchar(" + p_fieldSize + ")";
                    break;
                case 11: //ntext
                    fieldType = "ntext";
                    break;
                case 12: //numeric
                    fieldType = "numeric";
                    break;
                case 13: //nvarchar
                    fieldType = "nvarchar(" + p_fieldSize + ")";
                    break;
                case 14: //real
                    fieldType = "real";
                    break;
                case 15: //smalldatetime
                    fieldType = "smalldatetime";
                    break;
                case 16: //smallint
                    fieldType = "smallint";
                    break;
                case 17: //smallmoney
                    fieldType = "smallmoney";
                    break;
                case 18: //sql_variant
                    fieldType = "sql_variant";
                    break;
                case 19: //text
                    fieldType = "text";
                    break;
                case 20: //timestamp
                    fieldType = "timestamp";
                    break;
                case 21: //tinyint
                    fieldType = "tinyint";
                    break;
                case 22: //uniqueidentifier
                    fieldType = "uniqueidentifier";
                    break;
                case 23: //varbinary
                    fieldType = "varbinary";
                    break;
                case 24: //varchar
                    fieldType = "varchar(" + p_fieldSize + ")";
                    break;
            }
            string sql = "ALTER TABLE " + p_tableName + " ALTER COLUMN " + p_fieldName + " " + fieldType + "";
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 读取数据表中的字段及类型（Sql）
        /// </summary>
        /// <param name="p_tableName">数据表名</param>
        /// <param name="p_cn">连接数据库</param>
        /// <returns></returns>
        public string ReadSqlFieldAndTypes(string p_tableName, SqlConnection p_cn)
        {
            string strColumns = "";
            try
            {
                string[] res = { null, null, p_tableName, null };
                DataTable dtColumns = p_cn.GetSchema("Columns", res);
                foreach (DataRow rowColumns in dtColumns.Rows)
                {
                    strColumns += rowColumns[3].ToString() + "," + rowColumns[7].ToString() + "," + rowColumns[8].ToString() + "|";
                }
                if (strColumns != "")
                    strColumns = Common.CutEndStr(strColumns, "|");
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }
            return strColumns;
        }
        //0-数据库,1-dbo,2-表名,3-字段名,4-字段序列号,5-默认值，6-是否允许为空,7-字段类型,8-字段长度

        /// <summary>
        /// 创建Sql其它对象（存储过程、视图、索引、约束）
        /// </summary>
        /// <param name="p_sqlProcedureOrView">存储过程或视图</param>
        public void CreateSqlObject(string p_sqlObject, SqlConnection p_cn)
        {
            string sql = p_sqlObject;
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 删除存储过程（Sql）
        /// </summary>
        /// <param name="p_sqlProcedureName"></param>
        public void DropSqlProcedure(string p_sqlProcedureName, SqlConnection p_cn)
        {
            string sql = "DROP PROCEDURE " + p_sqlProcedureName;
            ExecuteSQLStmt(sql, p_cn);
        }

        /// <summary>
        /// 终止连接数据库（Sql）
        /// </summary>
        /// <param name="p_cn">连接数据库</param>
        public void TerminateSqlConn(SqlConnection p_cn)
        {
            try
            {
                if (p_cn.State == ConnectionState.Open)
                {
                    p_cn.Close();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        /// <summary>
        /// 创建通用Sql语句执行函数
        /// </summary>
        /// <param name="p_sql">Sql语句</param>
        /// <param name="p_cn">连接数据库</param>
        public void ExecuteSQLStmt(string p_sql, SqlConnection p_cn)
        {
            SqlCommand cmd = new SqlCommand(p_sql, p_cn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }
        }

        #endregion
    }
}